package com.myebills.ebpsfrontend.cust.controller;

import com.checkfree.isolutions.persist.PersistServer;import com.checkfree.isolutions.property.Properties;import com.checkfree.isolutions.property.PropertiesConfig;import com.checkfree.isolutions.utility.*;import java.sql.*;import java.text.DateFormat;import java.text.SimpleDateFormat;import org.apache.commons.net.ntp.TimeStamp;


public class AcctDetails{

	private String strAcctId = "";
	public AcctDetails(){
	}
	public AcctDetails(String acctId){		this.strAcctId = acctId;	}
    private static Connection getDbConnection() throws Exception {        Connection conn=null;    	String jdbcDriver = "";   		String jdbcDataSource = "";   		String username = "";   		String password = "";
        try{			com.checkfree.isolutions.property.Properties appCfg = com.checkfree.isolutions.property.PropertiesConfig.getApplicationSettings();
            if(appCfg.hasProperty("JDBCDriverName") == false){				throw new IllegalArgumentException("JDBC Driver not specified!");			}else{				jdbcDriver = appCfg.getProperty("JDBCDriverName");			}
            if(appCfg.hasProperty("JDBCDataSourceName") == false){				throw new IllegalArgumentException("JDBC Data Source not specified!");			}else{				jdbcDataSource = appCfg.getProperty("JDBCDataSourceName");			}
			if(appCfg.hasProperty("DataBaseUserid") == false){                throw new IllegalArgumentException("Database userid not specified!");			}else{                username = appCfg.getProperty("DataBaseUserid");			}
            if(appCfg.hasProperty("DataBasePassword") == false){                throw new IllegalArgumentException("Database password not specified!");            }else{                password = appCfg.getProperty("DataBasePassword");			}
			Class.forName(jdbcDriver);			conn = DriverManager.getConnection(jdbcDataSource, username, password);			System.out.println("--- Connection opened ---");		}catch(Exception e) {			throw new Exception("Error getting DB connection: " + e);		}
        return conn;    }// end getDbConnection

    public static void freeDbConnection(Connection conn) throws Exception {        try {			if (conn != null){				conn.close();				System.out.println("--- Connection closed ---");			}		}catch(Exception e) {            throw new Exception("Error closing DB connection : " + e);        }   }// end freeDbConnection
/***************************************************************************/
    public static void insertAcctDetails(String acctId,String acctKey,String custGroup,String supIndicator) throws Exception {        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				insertAcctDetails(connection, acctId, acctKey, custGroup, supIndicator);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging inserting account details table : " + e);        }    }

    public static void insertAcctDetails(Connection connection, String acctId,String acctKey,String custGroup,String supIndicator) throws Exception {        try {            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("INSERT INTO ACCOUNTDETAILS (ACCTID, ACCTKEY, CUSTGROUP, SUPINDICATOR) VALUES (?,?,?,?)");                pstmt.setString(1,acctId);                pstmt.setString(2,acctKey);                pstmt.setString(3,custGroup);                pstmt.setString(4,supIndicator);                pstmt.execute();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging inserting account details table : " + e);        }    }
/***************************************************************************/
    public static void updateAcctDetails(String acctId,String acctKey,String custGroup,String supIndicator) throws Exception {        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateAcctDetails(connection, acctId, acctKey, custGroup, supIndicator);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table : " + e);        }    }
    public static void updateAcctDetails(Connection connection, String acctId,String acctKey,String custGroup,String supIndicator) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET ACCTKEY = ?,CUSTGROUP = ?,SUPINDICATOR = ? WHERE ACCTID = ?");                pstmt.setString(1,acctKey);                pstmt.setString(2,custGroup);                pstmt.setString(3,supIndicator);                pstmt.setString(4,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table : " + e);        }    }    
/***************************************************************************/

    public static void updateAllAcctDetails(String acctId,String acctSubscription, String emailDeliverySubscription, String smsSubscription, String emailAdd, String smsNo) throws Exception {
        Connection connection = null;

        try {
			connection = getDbConnection();

            if(connection!=null){
				updateAllAcctDetails(connection,acctId, acctSubscription,emailDeliverySubscription,smsSubscription,emailAdd,smsNo);
            }
            freeDbConnection(connection);
        }
        catch(Exception e) {
            throw new Exception("Error logging updating account details table : " + e);
        }
    }        // added by tzer kang =====================================        public static void updateAllAcctDetailsCsr(String acctId,String acctSubscription, String emailDeliverySubscription, String smsSubscription, String emailAdd, String smsNo, String strDate, String strLastModifyDate,String userId,String channelId) throws Exception {        Connection connection = null;        try {			connection = getDbConnection();            if(connection!=null){            	updateAllAcctDetailsCsr(connection,acctId, acctSubscription,emailDeliverySubscription,smsSubscription,emailAdd,smsNo,strDate, strLastModifyDate,userId,channelId);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table : " + e);        }    }
    public static void updateAllAcctDetailsCsr(Connection connection,String acctId,String acctSubscription, String emailDeliverySubscription, String smsSubscription, String emailAdd, String smsNo, String strDate, String strLastModifyDate,String userId,String channelId) throws Exception {        try {        	PreparedStatement pstmt = null;            if(connection!=null){            	            	System.out.println("channelId :"+channelId);            	System.out.println("userId :"+userId);            	            	            	final DateFormat df = new SimpleDateFormat(EbppxConstants.DATE_FORMAT+" "+EbppxConstants.TIME_FORMAT);                java.sql.Timestamp registerDate = null;                if (!strDate.equals("")){                	registerDate = new Timestamp((df.parse(strDate)).getTime());                }                                java.sql.Timestamp modifyDate = null;                if (!strLastModifyDate.equals("")){                	modifyDate = new Timestamp((df.parse(strLastModifyDate)).getTime());                }                if(!acctSubscription.equals("")){                	                System.out.println("AcctSubscription :"+acctSubscription);                pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET ACCTSUBSCRIPTION = ?,EMAILDELIVERYSUBSCRIPTION = ?,SMSSUBSCRIPTION = ?,EMAILADD = ?,SMSNO = ?,REGISTER_DATE = ?,LASTMODIFYDATE = ?,UPDATEDBYCHANNEL = ?,UPDATEDBYUSER = ? WHERE ACCTID = ?");                pstmt.setString(1,acctSubscription);                pstmt.setString(2,emailDeliverySubscription);                pstmt.setString(3,smsSubscription);                pstmt.setString(4,emailAdd);                pstmt.setString(5,smsNo);                pstmt.setTimestamp(6, registerDate);                pstmt.setTimestamp(7, modifyDate);                pstmt.setString(8, channelId);                pstmt.setString(9, userId);                pstmt.setString(10,acctId);                pstmt.executeUpdate();                                }else{                	System.out.println("AcctSubscription :"+acctSubscription);                	pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET EMAILDELIVERYSUBSCRIPTION = ?,SMSSUBSCRIPTION = ?,EMAILADD = ?,SMSNO = ?,REGISTER_DATE = ?,LASTMODIFYDATE = ?,UPDATEDBYCHANNEL = ?,UPDATEDBYUSER = ? WHERE ACCTID = ?");                   // pstmt.setString(1,acctSubscription);                    pstmt.setString(1,emailDeliverySubscription);                     pstmt.setString(2,smsSubscription);                      pstmt.setString(3,emailAdd);                      pstmt.setString(4,smsNo);                      pstmt.setTimestamp(5, registerDate);                      pstmt.setTimestamp(6, modifyDate);                      pstmt.setString(7, channelId);                      pstmt.setString(8, userId);                      pstmt.setString(9,acctId);                      pstmt.executeUpdate();                	                	                	                	                }				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table : " + e);        }    }        //done added .=======================================    
    public static void updateAllAcctDetails(Connection connection,String acctId,String acctSubscription, String emailDeliverySubscription, String smsSubscription, String emailAdd, String smsNo) throws Exception {

        try {

            if(connection!=null){
                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET ACCTSUBSCRIPTION = ?,EMAILDELIVERYSUBSCRIPTION = ?,SMSSUBSCRIPTION = ?,EMAILADD = ?,SMSNO = ? WHERE ACCTID = ?");
                pstmt.setString(1,acctSubscription);
                pstmt.setString(2,emailDeliverySubscription);
                pstmt.setString(3,smsSubscription);
                pstmt.setString(4,emailAdd);
                pstmt.setString(5,smsNo);
                pstmt.setString(6,acctId);
                pstmt.executeUpdate();

				if (pstmt != null) {
					pstmt.close();
				}
            }
        }
        catch(Exception e) {
            throw new Exception("Error logging updating account details table : " + e);
        }
    }

//-----------------------------------Setter program--------------------------------------------------//
    public static void updateOldAcctKey(String acctId,String acctKey) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateOldAcctKey(connection, acctId, acctKey);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for old account key: " + e);        }    }

    public static void updateOldAcctKey(Connection connection, String acctId,String acctKey) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET ACCTKEY = ? WHERE ACCTID = ?");                pstmt.setString(1,acctKey);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for old account key: " + e);        }    }

    public static void updateCustGroup(String acctId,String custGroup) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateCustGroup(connection, acctId, custGroup);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for cust group: " + e);        }    }

    public static void updateCustGroup(Connection connection, String acctId,String custGroup) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET CUSTGROUP = ? WHERE ACCTID = ?");                pstmt.setString(1,custGroup);                pstmt.setString(2,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for cust group: " + e);        }    }


    public static void updateEmailAdd(String acctId,String emailAdd) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateEmailAdd(connection, acctId, emailAdd);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for email address: " + e);        }    }


    public static void updateEmailAdd(Connection connection, String acctId,String emailAdd) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET EMAILADD = ? WHERE ACCTID = ?");                pstmt.setString(1,emailAdd);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for email address: " + e);        }    }        public static void updateRegisterDate(Connection connection, String acctId,String strRegisterDate) throws Exception {        try {        	final DateFormat df = new SimpleDateFormat(EbppxConstants.DATE_FORMAT+" "+EbppxConstants.TIME_FORMAT);        	java.sql.Timestamp registerDate = null;        	             if(connection!=null){            	            	                if (!strRegisterDate.equals("")){                	registerDate = new Timestamp((df.parse(strRegisterDate)).getTime());                }                                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET REGISTER_DATE = ? WHERE ACCTID = ?");                pstmt.setTimestamp(1,registerDate);                pstmt.setString(2,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for RegisterDate: " + e);        }    }        public static void updateAcctInfoSub(Connection connection,String acctId,String emailSub ,String registerDate,String lastModifiedDate ,String updatedByChannel,String updatedByUser) throws Exception {        try {        	final DateFormat df = new SimpleDateFormat(EbppxConstants.DATE_FORMAT+" "+EbppxConstants.TIME_FORMAT);        	java.sql.Timestamp modifyDate = null;        	java.sql.Timestamp registerDateF = null;        	            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET EMAILDELIVERYSUBSCRIPTION = ?,REGISTER_DATE = ? ,LASTMODIFYDATE = ? ,UPDATEDBYCHANNEL = ?,UPDATEDBYUSER = ? WHERE ACCTID = ?");                               System.out.println("registerDate :"+registerDate);               if (!lastModifiedDate.equals("")){                	modifyDate = new Timestamp((df.parse(lastModifiedDate)).getTime());                	System.out.println("modifyDate :"+modifyDate);                }                if(!registerDate.equals("")){                	                	registerDateF = new Timestamp((df.parse(registerDate)).getTime());                	System.out.println("registerDateF :"+registerDateF);                }                              pstmt.setString(1,emailSub);                pstmt.setTimestamp(2,modifyDate);                pstmt.setTimestamp(3,registerDateF);                pstmt.setString(4,updatedByChannel);                pstmt.setString(5,updatedByUser);                pstmt.setString(6,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for acctDetails: " + e);        }    }        public static void updateAcctInfo(Connection connection,String acctId,String emailAddr,String emailSub ,String registerDate,String lastModifiedDate ,String updatedByChannel,String updatedByUser) throws Exception {        try {        	final DateFormat df = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");        	java.sql.Timestamp modifyDate = null;        	java.sql.Timestamp registerDateF = null;        	        	            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET EMAILADD = ?,EMAILDELIVERYSUBSCRIPTION = ?,REGISTER_DATE = ? ,LASTMODIFYDATE = ? ,UPDATEDBYCHANNEL = ?,UPDATEDBYUSER = ? WHERE ACCTID = ?");               if (!lastModifiedDate.equals("")){                	modifyDate = new Timestamp((df.parse(lastModifiedDate)).getTime());                             }                if(!registerDate.equals("")){                	                	registerDateF = new Timestamp((df.parse(registerDate)).getTime());                               }                pstmt.setString(1,emailAddr);                pstmt.setString(2,emailSub);                pstmt.setTimestamp(3,modifyDate);                pstmt.setTimestamp(4,registerDateF);                pstmt.setString(5,updatedByChannel);                pstmt.setString(6,updatedByUser);                pstmt.setString(7,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for acctDetails: " + e);        }    }    

    public static void updateSMSNo(String acctId,String smsNo) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateSMSNo(connection, acctId, smsNo);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sms no: " + e);        }    }


    public static void updateSMSNo(Connection connection, String acctId,String smsNo) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET SMSNO = ? WHERE ACCTID = ?");                pstmt.setString(1,smsNo);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();
				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sms no: " + e);        }    }




    public static void updateAcctSubscription(String acctId,String acctSubscription) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateAcctSubscription(connection, acctId, acctSubscription);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for account subscription: " + e);        }    }



    public static void updateAcctSubscription(Connection connection, String acctId,String acctSubscription) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET ACCTSUBSCRIPTION = ? WHERE ACCTID = ?");                pstmt.setString(1,acctSubscription);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for account subscription: " + e);        }    }


    public static void updateEmailDeliverySubscription(String acctId,String emailDeliverySubscription) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateEmailDeliverySubscription(connection, acctId, emailDeliverySubscription);				connection.commit();//ebps            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for email delivery subscription: " + e);        }    }



    public static void updateEmailDeliverySubscription(Connection connection, String acctId,String emailDeliverySubscription) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET EMAILDELIVERYSUBSCRIPTION = ? WHERE ACCTID = ?");                pstmt.setString(1,emailDeliverySubscription);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for email delivery subscription: " + e);        }    }

    public static void updateSMSSubscription(String acctId,String smsSubscription) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateSMSSubscription(connection, acctId, smsSubscription);           }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sms subscription: " + e);        }    }



    public static void updateSMSSubscription(Connection connection, String acctId,String smsSubscription) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET SMSSUBSCRIPTION = ? WHERE ACCTID = ?");                pstmt.setString(1,smsSubscription);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sms subscription: " + e);        }    }



    public static void updateLastViewedDate(String acctId,String lastViewedDate) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateLastViewedDate(connection, acctId, lastViewedDate);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for last viewed date: " + e);        }    }


    public static void updateLastViewedDate(Connection connection, String acctId,String lastViewedDate) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET LASTVIEWEDDATE = ? WHERE ACCTID = ?");                pstmt.setString(1,lastViewedDate);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for last viewed date: " + e);        }    }



    public static void updateSupIndicator(String acctId,String supIndicator) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateSupIndicator(connection, acctId, supIndicator);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sup indicator: " + e);        }    }



    public static void updateSupIndicator(Connection connection, String acctId,String supIndicator) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET SUPINDICATOR = ? WHERE ACCTID = ?");                pstmt.setString(1,supIndicator);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sup indicator: " + e);        }    }



////-----------------------------------------Getter program---------------------------------------------////
	/**
			To retrieve acctid regardless user input is new/old account no.
	*/
//////////////////////////////////////////////////////////////////////////////////////    public static EbppxAcctDetailsDataBean getAcctDetails(Connection connection,String acctNo)throws Exception {
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
        EbppxAcctDetailsDataBean acctDataBean = new EbppxAcctDetailsDataBean();
        String strNewAccountKey = "",strOldAccountKey="",strCustGroup="",strEmailAdd="",strSMSNo="",
        	   strAcctSubscription="",strEmailDeliverySubscription="",strSMSSubscription,strSupIndicator="";
        Date dateRegisteredDate=null;
        try{
			if (connection!=null){

				//search for status records first
				query.append("SELECT TRIM(ACCTID),TRIM(ACCTKEY),TRIM(CUSTGROUP),TRIM(EMAILADD),TRIM(SMSNO),TRIM(EMAILDELIVERYSUBSCRIPTION),TRIM(SMSSUBSCRIPTION),TRIM(SUPINDICATOR),REGISTER_DATE, LASTMODIFYDATE FROM ACCOUNTDETAILS WHERE ACCTID = ? OR ACCTKEY = ?");

				// prepare the statement and bind parameter
				pstmt = connection.prepareStatement(query.toString());
				pstmt.setString(1,acctNo);
				pstmt.setString(2,acctNo);

				rs   = pstmt.executeQuery();

				if(rs.next()){
					System.out.println("In account details get details.....");
					acctDataBean.setAcctId(rs.getString(1));//strNewAccountKey
					acctDataBean.setOldAcctKey(rs.getString(2));//strOldAccountKey
					acctDataBean.setCustGroup(rs.getString(3));//strCustGroup
					acctDataBean.setEmailAdd(rs.getString(4));//strEmailAdd
					acctDataBean.setSMSNo(rs.getString(5));//strSMSNo
					//acctDataBean.setAcctSubscription(rs.getString(6));//strAcctSubscription
					acctDataBean.setEmailSubscription(rs.getString(6));//strEmailDeliverySubscription
					acctDataBean.setSMSSubscription(rs.getString(7));//strSMSSubscription
					acctDataBean.setSupIndicator(rs.getString(8));//strSupIndicator					acctDataBean.setRegisterDate(rs.getTimestamp(9));//dateRegisterDate					acctDataBean.setLastModifyDate(rs.getTimestamp(10));//dateLastModifyDate
					System.out.println("End account details get details.....");
				}

				if (pstmt != null) {
					pstmt.close();
				}				if (rs != null) {					rs.close();				}
			}

			System.out.println("Retrieve acctdetails regardless user input is new/old account no. has been successfully executed");

		}catch(Exception e){
			throw new Exception("" + e);
		}finally{
			if(rs!=null){
				rs.close();
			}
		}
		return acctDataBean;
	}


    public static EbppxAcctDetailsDataBean getAcctDetails(String acctNo)throws Exception {
		Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
        EbppxAcctDetailsDataBean acctDataBean = new EbppxAcctDetailsDataBean();

        try{
			connection = getDbConnection();

			if (connection!=null){
				acctDataBean = getAcctDetails(connection,acctNo);
			}

            System.out.println("Retrieve acctdetails regardless user input is new/old account no. has been successfully executed");

		}catch(Exception e){
            throw new Exception("" + e);
        }finally{
            if(rs!=null){
                rs.close();
            }
            if(connection!=null){
                freeDbConnection(connection);
            }
        }
        return acctDataBean;
    }
////////////////////////////////////////////////////////////////////////////////////////////////////
	public static String getAcctId(Connection connection,String acctNo)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strNewAccountKey = "";
        try{			if (connection!=null){				//search for status records first				query.append("SELECT TRIM(ACCTID) FROM ACCOUNTDETAILS WHERE ACCTID = ? ");
				// prepare the statement and bind parameter				pstmt = connection.prepareStatement(query.toString());				pstmt.setString(1,acctNo);
				rs   = pstmt.executeQuery();
				if(rs.next()){					strNewAccountKey = rs.getString(1);				}				if (pstmt != null) {					pstmt.close();				}
			}
			System.out.println("Retrieve acctid regardless user input is new/old account no. has been successfully executed");		}catch(Exception e){			throw new Exception("" + e);		}finally{			if(rs!=null){				rs.close();			}		}
        if (strNewAccountKey == null){			strNewAccountKey = "";		}		return strNewAccountKey;	}

    public static String getAcctId(String acctNo)throws Exception {		Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strNewAccountKey = "";
        try{			connection = getDbConnection();
			if (connection!=null){				strNewAccountKey = getAcctId(connection,acctNo);			}

            System.out.println("Retrieve acctid regardless user input is new/old account no. has been successfully executed");
		}catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strNewAccountKey;    }

    public static String getAcctKey(Connection connection,String acctNo)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strOldAccountKey = "";
        try{                if (connection!=null){
                    //search for status records first                    query.append("SELECT TRIM(ACCTKEY) FROM ACCOUNTDETAILS WHERE ACCTID = ? ");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctNo);
                    rs   = pstmt.executeQuery();
					if(rs.next()){						strOldAccountKey = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }
            System.out.println("Retrieve acctKey regardless user input is new/old account no. has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }        }
        if (strOldAccountKey == null){			strOldAccountKey = "";		}        return strOldAccountKey;    }        public static Date getRegisterDate(Connection connection,String acctNo)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        Timestamp stamp = new Timestamp(System.currentTimeMillis());                Date registerDate = null ;             try{                if (connection!=null){                    //search for status records first                    query.append("SELECT REGISTER_DATE FROM ACCOUNTDETAILS WHERE ACCTID = ? ");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctNo);                    rs   = pstmt.executeQuery();					if(rs.next()){						stamp =  rs.getTimestamp(1);						if(stamp != null){							registerDate = new Date(stamp.getTime());						}																	}                    if (pstmt != null) {                        pstmt.close();                    }                }            System.out.println("Retrieve acctKey regardless user input is new/old account no. has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }        }              return registerDate;    }        public static Date getLastModifyDate(Connection connection,String acctNo)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        Date lastModifyDate = null;        Timestamp stamp = new Timestamp(System.currentTimeMillis());        try{                if (connection!=null){                    //search for status records first                    query.append("SELECT TRIM(LASTMODIFYDATE) FROM ACCOUNTDETAILS WHERE ACCTID = ? ");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctNo);                    rs   = pstmt.executeQuery();					if(rs.next()){						stamp =  rs.getTimestamp(1);						lastModifyDate = new Date(stamp.getTime());										}                    if (pstmt != null) {                        pstmt.close();                    }                }            System.out.println("Retrieve lastModifyDate regardless user input is new/old account no. has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }        }              return lastModifyDate;    }

    public static String getAcctKey(String acctNo)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strOldAccountKey = "";
        try{            connection = getDbConnection();
            if (connection!=null){				strOldAccountKey = getAcctKey(connection,acctNo);            }            System.out.println("Retrieve acctKey regardless user input is new/old account no. has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strOldAccountKey;    }

    public static String getOldAccountKey(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strOldAccountKey = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT TRIM(ACCTKEY) FROM ACCOUNTDETAILS WHERE ACCTID = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);
                    rs   = pstmt.executeQuery();
					if(rs.next()){						strOldAccountKey = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }
            System.out.println("Retrieve Old Account key with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }        }
        if (strOldAccountKey == null){			strOldAccountKey = "";		}
        return strOldAccountKey;    }
    public static String getOldAccountKey(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strOldAccountKey = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strOldAccountKey = getOldAccountKey(connection,acctId);                }
            System.out.println("Retrieve Old Account key with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strOldAccountKey;    }

    public static String getCustGroup(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strCustGroup = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT TRIM(CUSTGROUP) FROM ACCOUNTDETAILS WHERE ACCTID = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);
                    rs   = pstmt.executeQuery();
					if(rs.next()){						strCustGroup = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }            System.out.println("Retrieve custgroup with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }        }        if (strCustGroup == null){			strCustGroup = "";		}        return strCustGroup;    }


    public static String getCustGroup(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strCustGroup = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strCustGroup = getCustGroup(connection, acctId);                }            System.out.println("Retrieve custgroup with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strCustGroup;    }


    public static String getEmailAdd(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strEmailAdd = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT TRIM(EMAILADD) FROM ACCOUNTDETAILS WHERE ACCTID = ? OR ACCTKEY = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    pstmt.setString(2,acctId);
                    rs   = pstmt.executeQuery();
					if(rs.next()){						strEmailAdd = rs.getString(1);
					}
                    if (pstmt != null) {                        pstmt.close();                    }                }            System.out.println("Retrieve email address with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }        }        if (strEmailAdd == null){			strEmailAdd = "";		}        return strEmailAdd;    }

    public static String getEmailAdd(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strEmailAdd = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strEmailAdd = getEmailAdd(connection, acctId);                }            System.out.println("Retrieve email address with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strEmailAdd;    }

    public static String getSMSNo(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSMSNo = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT TRIM(SMSNO) FROM ACCOUNTDETAILS WHERE ACCTID = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);
                    rs   = pstmt.executeQuery();
					if(rs.next()){						strSMSNo = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }
            System.out.println("Retrieve SMS no with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }
        if (strSMSNo == null){			strSMSNo = "";		}
        return strSMSNo;    }

    public static String getSMSNo(String acctId)throws Exception {
        Connection connection = null;
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSMSNo = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strSMSNo = getSMSNo(connection, acctId);                }
            System.out.println("Retrieve SMS no with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strSMSNo;    }

    public static String getAcctSubscription(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strAcctSubscription = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT TRIM(ACCTSUBSCRIPTION) FROM ACCOUNTDETAILS WHERE ACCTID = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    rs   = pstmt.executeQuery();
					if(rs.next()){						strAcctSubscription = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }
            System.out.println("Retrieve account subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }        }
        if (strAcctSubscription == null){			strAcctSubscription = "";		}        return strAcctSubscription;    }

    public static String getAcctSubscription(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strAcctSubscription = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strAcctSubscription = getAcctSubscription(connection, acctId);                }
            System.out.println("Retrieve account subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strAcctSubscription;    }

    public static String getEmaiDeliverySubscription(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strEmailDeliverySubscription = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT TRIM(EMAILDELIVERYSUBSCRIPTION) FROM ACCOUNTDETAILS WHERE ACCTID = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);
                    rs   = pstmt.executeQuery();
					if(rs.next()){						strEmailDeliverySubscription = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }
            System.out.println("Retrieve email delivery subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }        }
        if (strEmailDeliverySubscription == null){			strEmailDeliverySubscription = "";		}
        return strEmailDeliverySubscription;    }

    public static String getEmaiDeliverySubscription(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strEmailDeliverySubscription = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strEmailDeliverySubscription = getEmaiDeliverySubscription(connection, acctId);                }
            System.out.println("Retrieve email delivery subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);
        }finally{            if(rs!=null){                rs.close();            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strEmailDeliverySubscription;    }

    public static String getSMSSubscription(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSMSSubscription = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT TRIM(SMSSUBSCRIPTION) FROM ACCOUNTDETAILS WHERE ACCTID = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);
                    rs   = pstmt.executeQuery();					if(rs.next()){						strSMSSubscription = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                   }                }            System.out.println("Retrieve SMS subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }        }
        if (strSMSSubscription == null){			strSMSSubscription = "";		}        return strSMSSubscription;    }

    public static String getSMSSubscription(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSMSSubscription = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strSMSSubscription = getSMSSubscription(connection, acctId);                }
            System.out.println("Retrieve SMS subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strSMSSubscription;    }


    public static String getSupIndicator(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSupIndicator= "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT TRIM(SUPINDICATOR) FROM ACCOUNTDETAILS WHERE ACCTID = ? OR ACCTKEY= ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    pstmt.setString(2,acctId);

                    rs   = pstmt.executeQuery();
					if(rs.next()){						strSupIndicator = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }
            System.out.println("Retrieve last viewed date with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }        }
        if (strSupIndicator == null){			strSupIndicator = "";		}
        return strSupIndicator;    }

    public static String getSupIndicator(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSupIndicator = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strSupIndicator = getSupIndicator(connection, acctId);                }
            System.out.println("Retrieve last viewed date with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strSupIndicator;    }
    public static String getCorpCust(Connection connection,String acctId)throws Exception {

        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
        String strCorpCust= "";

        try{

                if (connection!=null){

                    //search for status records first
                    query.append("SELECT TRIM(CORPCUST) FROM ACCOUNTDETAILS WHERE ACCTID = '"+acctId+"' ");

                    // prepare the statement and bind parameter
                    pstmt = connection.prepareStatement(query.toString());

                    rs   = pstmt.executeQuery();

					if(rs.next()){
						strCorpCust = rs.getString(1);
					}

                    if (pstmt != null) {
                        pstmt.close();
                    }
                }

            System.out.println("Retrieve corporate customer with account ID has been successfully executed");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
            if(rs!=null){
                rs.close();
            }
        }

        return strCorpCust;
    }


    public static String getCorpCust(String acctId)throws Exception {

        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
        String strCorpCust = "";

        try{
            connection = getDbConnection();

                if (connection!=null){
					strCorpCust = getCorpCust(connection, acctId);
                }

            System.out.println("Retrieve corporate customer with account ID has been successfully executed");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
            if(rs!=null){
                rs.close();
            }
            if(connection!=null){
                freeDbConnection(connection);
            }
        }
        return strCorpCust;
    }

    public static boolean checkIfExistInAcctDetails(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        int intResult = 0;		boolean boolResult = false;
        try{
            connection = getDbConnection();
            if (connection!=null){				boolResult = checkIfExistInAcctDetails(connection, acctId);            }            System.out.println("Check if the record exist in account details has successfully been executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(connection!=null){                freeDbConnection(connection);            }
            if(rs != null){                rs.close();            }        }        return boolResult;    }
    public static boolean isOldAccountKey(String acctNo)throws Exception {
		boolean result = false;

		try{
			String strOldAccountKey = getAcctKey(acctNo);
			result = (EbppxUtil.nullToEmpty(strOldAccountKey).equals("") || !(EbppxUtil.nullToEmpty(strOldAccountKey).equals(EbppxUtil.nullToEmpty(acctNo)))) ? false : true;
			return result;

		}catch(Exception e){
			throw new Exception("" + e);
		}
	}

    public static boolean checkIfExistInAcctDetails(Connection connection, String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        int intResult = 0;		boolean boolResult = false;
        try{
            if (connection!=null){
				//search for status records first				query.append("SELECT COUNT(ACCTID) FROM ACCOUNTDETAILS WHERE ACCTID= ?");                // prepare the statement and bind parameter                pstmt = connection.prepareStatement(query.toString());                pstmt.setString(1,acctId);
                rs   = pstmt.executeQuery();
				if(rs.next()){					intResult = rs.getInt(1);				}
				if(intResult!=0){					boolResult = true;				}
                if (pstmt != null) {                    pstmt.close();                }            }            System.out.println("Check if the record exist in account details has successfully been executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{
            if(rs!=null){                rs.close();            }        }        return boolResult;    }        }








